package com.zenika.zencontact.model;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;

public class JdbcContactDao implements ContactDao {

	private static final String INSERT = "insert into contacts (firstname, lastname, birthdate,email, notes, active, category,language) values (?,?,?,?,?,?,?,?)";
	private static final String UPDATE = "update contacts set firstname=?, lastname=?, birthdate=?, email=?, notes=?,active=?, category=?,language=? where id=?";
	private static final String DELETE = "delete from contacts where id=?";
	private static final String GET = "select * from contacts where id=?";
	private static final String FIND = "select * from contacts where (firstname like ? or lastname like ? or email like ? or notes like ?) order by lastname, firstname";
	private static final String CREATE_CONTACTS_TABLE = "create table contacts (id bigint generated by default as identity (start with 1) not null primary key, firstName varchar(15), lastName varchar(20), birthdate date, email varchar(150), notes varchar(500), active boolean,category varchar(15),language varchar(15))";

	private DataSource dataSource;

	public void setDataSource(DataSource ds) {
		this.dataSource = ds;
	}

	public void save(Contact c) {
		if (c.getId() == null) {
			new JdbcTemplate(dataSource).update(INSERT, new Object[] { c.getFirstName(), c.getLastName(),
					c.getBirthDate(), c.getEmail(), c.getNotes(), Boolean.valueOf(c.isActive()), c.getCategory(),
					c.getLanguage() });
		} else {
			new JdbcTemplate(dataSource).update(UPDATE, new Object[] { c.getFirstName(), c.getLastName(),
					c.getBirthDate(), c.getEmail(), c.getNotes(), Boolean.valueOf(c.isActive()), c.getCategory(),
					c.getLanguage(), c.getId() });
		}
	}

	public void delete(Long contactId) {
		new JdbcTemplate(dataSource).update(DELETE, new Object[] { contactId }, new int[] { Types.BIGINT });
	}

	public Contact get(Long contactId) {
		return (Contact) new JdbcTemplate(dataSource).queryForObject(GET, new Object[] { contactId },
				new int[] { Types.BIGINT }, new ContactMapper());
	}

	public List find(String searchString) {
		// if ("*".equals(searchString))
		// searchString = "%";

		final String ss = (searchString == null || searchString.length() == 0) ? "%" : searchString + "%";

		return (List) new JdbcTemplate(dataSource).query(FIND, new PreparedStatementSetter() {
			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, ss);
				ps.setString(2, ss);
				ps.setString(3, ss);
				ps.setString(4, ss);
			}
		}, new ContactMapper());
	}

	private static final class ContactMapper implements RowMapper {
		public Object mapRow(ResultSet resultSet, int i) throws SQLException {
			Contact c = new Contact();
			c.setId(new Long(resultSet.getLong("id")));
			c.setFirstName(resultSet.getString("firstname"));
			c.setLastName(resultSet.getString("lastname"));
			c.setBirthDate(resultSet.getDate("birthdate"));
			c.setEmail(resultSet.getString("email"));
			c.setNotes(resultSet.getString("notes"));
			c.setActive(resultSet.getBoolean("active"));
			c.setCategory(resultSet.getString("category"));
			c.setLanguage(resultSet.getString("language"));
			return c;
		}
	}

	public void createDatabase() {
		JdbcTemplate jdbc = new JdbcTemplate(dataSource);
		jdbc.update(CREATE_CONTACTS_TABLE);
	}
}
